﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class YX_BankBillBLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT r,
       N_BANKBILL_ID,
       CMY_NAME1,
       C_BANKBILL_DRAWERACCOUNT,
       C_NAME1,
       CMY_NAME2,
       C_BANKBILL_PAYEEACCOUNT,
       C_NAME2,
       N_BANKBILL_INVOICEVALUE,
       to_char(D_BANKBILL_STARTDATE, 'yyyy-mm-dd') D_BANKBILL_STARTDATE,
       to_char(D_BANKBILL_ENDDATE, 'yyyy-mm-dd') D_BANKBILL_ENDDATE,
       C_BANKBILL_CODE
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               T1.N_BANKBILL_ID AS N_BANKBILL_ID,
               T2.CMY_NAME AS CMY_NAME1,
               T1.C_BANKBILL_DRAWERACCOUNT AS C_BANKBILL_DRAWERACCOUNT,
               T3.C_NAME AS C_NAME1,
               T4.CMY_NAME AS CMY_NAME2,
               T1.C_BANKBILL_PAYEEACCOUNT AS C_BANKBILL_PAYEEACCOUNT,
               T5.C_NAME AS C_NAME2,
               T1.N_BANKBILL_INVOICEVALUE AS N_BANKBILL_INVOICEVALUE,
               T1.D_BANKBILL_STARTDATE AS D_BANKBILL_STARTDATE,
               T1.D_BANKBILL_ENDDATE AS D_BANKBILL_ENDDATE,
               T1.C_BANKBILL_CODE AS C_BANKBILL_CODE
          FROM YX_BANKBILL T1,
               SYS_COMPANY T2,
               SYS_BANK    T3,
               SYS_COMPANY T4,
               SYS_BANK    T5
         WHERE T2.CMY_ID = T1.N_BANKBILL_DRAWERCOMPANYID
           AND T3.N_ID = T1.N_BANKBILL_DRAWERBANKID
           AND T4.CMY_ID = T1.N_BANKBILL_PAYEECOMPANYID
           AND T5.N_ID = T1.N_BANKBILL_PAYEEBANKID ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public DataTable GetListByPage2(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT r,
       N_BANKBILL_ID,
       CMY_NAME1,
       C_BANKBILL_DRAWERACCOUNT,
       C_NAME1,
       CMY_NAME2,
       C_BANKBILL_PAYEEACCOUNT,
       C_NAME2,
       N_BANKBILL_INVOICEVALUE,
       D_BANKBILL_STARTDATE,
       D_BANKBILL_ENDDATE,
       C_BANKBILL_CODE,
       N_BANKBILL_IFOFFER,
       C_BANKBILL_REMARK
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
               YXBL.N_BANKBILL_ID AS N_BANKBILL_ID,
               SYSC1.CMY_NAME AS CMY_NAME1,
               YXBL.C_BANKBILL_DRAWERACCOUNT AS C_BANKBILL_DRAWERACCOUNT,
               SYSB1.C_NAME AS C_NAME1,
               SYSC2.CMY_NAME AS CMY_NAME2,
               YXBL.C_BANKBILL_PAYEEACCOUNT AS C_BANKBILL_PAYEEACCOUNT,
               SYSB2.C_NAME AS C_NAME2,
               YXBL.N_BANKBILL_INVOICEVALUE AS N_BANKBILL_INVOICEVALUE,
               YXBL.D_BANKBILL_STARTDATE AS D_BANKBILL_STARTDATE,
               YXBL.D_BANKBILL_ENDDATE AS D_BANKBILL_ENDDATE,
               YXBL.C_BANKBILL_CODE AS C_BANKBILL_CODE,
               decode(YXBL.N_BANKBILL_IFOFFER, 1, '能', 0, '不能') AS N_BANKBILL_IFOFFER,
               YXBL.C_BANKBILL_REMARK AS C_BANKBILL_REMARK
          FROM YX_BANKBILL      YXBL,
               YX_ACCOUNT       YXAT,
               SYS_COMPANY      SYSC1,
               SYS_BANK         SYSB1,
               YX_CUSTOMER      YXCR,
               YX_CUSTOMER_BANK YXCB,
               SYS_COMPANY      SYSC2,
               SYS_BANK         SYSB2
         WHERE YXAT.ACCOUNTNO = YXBL.C_BANKBILL_DRAWERACCOUNT
           AND YXAT.COMPANY_ID = SYSC1.CMY_ID
           AND YXAT.N_BANK_ID = SYSB1.N_ID
           AND YXCB.C_NUMBER = YXBL.C_BANKBILL_PAYEEACCOUNT
           AND YXCR.N_ID = YXCB.N_CUSTOMER_ID
           AND YXCR.N_COMPANY_ID = SYSC2.CMY_ID
           AND YXCB.N_BANK_ID = SYSB2.N_ID
           AND YXCR.N_COMPANY_ID = YXBL.N_BANKBILL_PAYEECOMPANYID
           AND YXCB.N_BANK_ID = YXBL.N_BANKBILL_PAYEEBANKID
           AND YXAT.COMPANY_ID = YXBL.N_BANKBILL_DRAWERCOMPANYID
           AND YXAT.N_BANK_ID = YXBL.N_BANKBILL_DRAWERBANKID ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr, string operName)
        {
            string sql = string.Format(@"SELECT count(1)
          FROM YX_BANKBILL      YXBL,
               YX_ACCOUNT       YXAT,
               SYS_COMPANY      SYSC1,
               SYS_BANK         SYSB1,
               YX_CUSTOMER      YXCR,
               YX_CUSTOMER_BANK YXCB,
               SYS_COMPANY      SYSC2,
               SYS_BANK         SYSB2
         WHERE YXAT.ACCOUNTNO = YXBL.C_BANKBILL_DRAWERACCOUNT
           AND YXAT.COMPANY_ID = SYSC1.CMY_ID
           AND YXAT.N_BANK_ID = SYSB1.N_ID
           AND YXCB.C_NUMBER = YXBL.C_BANKBILL_PAYEEACCOUNT
           AND YXCR.N_ID = YXCB.N_CUSTOMER_ID
           AND YXCR.N_COMPANY_ID = SYSC2.CMY_ID
           AND YXCB.N_BANK_ID = SYSB2.N_ID
           AND YXCR.N_COMPANY_ID = YXBL.N_BANKBILL_PAYEECOMPANYID
           AND YXCB.N_BANK_ID = YXBL.N_BANKBILL_PAYEEBANKID
           AND YXAT.COMPANY_ID = YXBL.N_BANKBILL_DRAWERCOMPANYID
           AND YXAT.N_BANK_ID = YXBL.N_BANKBILL_DRAWERBANKID " + whereStr);

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 根据编号查询银行汇票明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_BankBill SelectById(string id)
        {
            string sql = string.Format(@"select * from YX_BankBill a where a.N_BANKBILL_ID= " + id);

            DataTable dt = Query(sql);

            YX_BankBill model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new YX_BankBill();
                    model.N_BANKBILL_ID = Convert.ToDecimal(id);
                    model.N_BANKBILL_DRAWERCOMPANYID = decimal.Parse(dr["N_BANKBILL_DRAWERCOMPANYID"].ToString());
                    model.N_BANKBILL_DRAWERBANKID = decimal.Parse(dr["N_BANKBILL_DRAWERBANKID"].ToString());
                    model.C_BANKBILL_DRAWERACCOUNT = dr["C_BANKBILL_DRAWERACCOUNT"].ToString();
                    model.N_BANKBILL_PAYEECOMPANYID = decimal.Parse(dr["N_BANKBILL_PAYEECOMPANYID"].ToString());
                    model.N_BANKBILL_PAYEEBANKID = decimal.Parse(dr["N_BANKBILL_PAYEEBANKID"].ToString());
                    model.C_BANKBILL_PAYEEACCOUNT = dr["C_BANKBILL_PAYEEACCOUNT"].ToString();
                    model.D_BANKBILL_STARTDATE = Convert.ToDateTime(dr["D_BANKBILL_STARTDATE"].ToString());
                    model.D_BANKBILL_ENDDATE = Convert.ToDateTime(dr["D_BANKBILL_ENDDATE"].ToString());
                    model.N_BANKBILL_INVOICEVALUE = Convert.ToDecimal(dr["N_BANKBILL_INVOICEVALUE"].ToString());
                    model.C_BANKBILL_CODE = dr["C_BANKBILL_CODE"].ToString();
                    model.N_BANKBILL_IFOFFER = Convert.ToDecimal(dr["N_BANKBILL_IFOFFER"].ToString());
                    model.C_BANKBILL_REMARK = dr["C_BANKBILL_REMARK"].ToString();
                }
            }

            return model;
        }

        public int INSERT(YX_BankBill model, string operName)
        {
            model.N_BANKBILL_ID = GetSequence("SEQ_N_BANKBILL_ID");
            string sql = string.Format(@"insert into yx_bankbill
  (n_bankbill_id,
   n_bankbill_drawercompanyid,
   c_bankbill_draweraccount,
   n_bankbill_drawerbankid,
   n_bankbill_payeecompanyid,
   c_bankbill_payeeaccount,
   n_bankbill_payeebankid,
   n_bankbill_invoicevalue,
   d_bankbill_startdate,
   d_bankbill_enddate,
   c_bankbill_code,
   n_bankbill_ifoffer,
   c_bankbill_remark)
values
  ({0},
   {1},
   '{2}',
   {3},
   {4},
   '{5}',
   {6},
   {7},
   TO_DATE('{8}', 'YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('{9}', 'YYYY-MM-DD HH24:MI:SS'),
   '{10}',
   {11},
'{12}')", model.N_BANKBILL_ID, model.N_BANKBILL_DRAWERCOMPANYID, model.C_BANKBILL_DRAWERACCOUNT, model.N_BANKBILL_DRAWERBANKID, model.N_BANKBILL_PAYEECOMPANYID, model.C_BANKBILL_PAYEEACCOUNT, model.N_BANKBILL_PAYEEBANKID, model.N_BANKBILL_INVOICEVALUE, model.D_BANKBILL_STARTDATE, model.D_BANKBILL_ENDDATE, model.C_BANKBILL_CODE, model.N_BANKBILL_IFOFFER, model.C_BANKBILL_REMARK);

            int count = NoQuery(sql, "新增银行汇票", "INSERT", operName);
            return count;
        }

        public int UPDATE(YX_BankBill model, string operName)
        {
            string sql = string.Format(@"update yx_bankbill
   set n_bankbill_drawercompanyid = " + model.N_BANKBILL_DRAWERCOMPANYID + @",
       c_bankbill_draweraccount = '" + model.C_BANKBILL_DRAWERACCOUNT + @"',
       n_bankbill_drawerbankid = " + model.N_BANKBILL_DRAWERBANKID + @",
       n_bankbill_payeecompanyid = " + model.N_BANKBILL_PAYEECOMPANYID + @",
       c_bankbill_payeeaccount = '" + model.C_BANKBILL_PAYEEACCOUNT + @"',
       n_bankbill_payeebankid = " + model.N_BANKBILL_PAYEEBANKID + @",
       n_bankbill_invoicevalue = " + model.N_BANKBILL_INVOICEVALUE + @",
       d_bankbill_startdate = TO_DATE('" + model.D_BANKBILL_STARTDATE.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD'),
       d_bankbill_enddate = TO_DATE('" + model.D_BANKBILL_ENDDATE.ToString("yyyy-MM-dd") + @"','YYYY-MM-DD'),
       c_bankbill_code = '" + model.C_BANKBILL_CODE + @"',
       n_bankbill_ifoffer = " + model.N_BANKBILL_IFOFFER + @",
       c_bankbill_remark = '" + model.C_BANKBILL_REMARK + @"'
 where n_bankbill_id = " + model.N_BANKBILL_ID);
            int count = NoQuery(sql, operName);
            return count;
        }
    }
}